package com.oa.lms.apps.dao.absence.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.oa.lms.apps.constants.IOrderStatus;
import com.oa.lms.apps.dao.absence.IAbsenceOrderDao;
import com.oa.lms.apps.entity.absence.AbsenceOrderValue;


public class AbsenceOrderDaoImpl extends HibernateDaoSupport implements
		IAbsenceOrderDao {

	private List<AbsenceOrderValue> list;
	
	private Integer totalNumber;

	@Override
	public Integer add(AbsenceOrderValue absenceOrderValue) {
		if (absenceOrderValue != null) {
			this.getHibernateTemplate().save(absenceOrderValue);
		}
		return absenceOrderValue.getOrderId();
	}

	@Override
	public void modify(AbsenceOrderValue absenceOrderValue) {
		if (absenceOrderValue != null) {
			this.getHibernateTemplate().update(absenceOrderValue);
		}

	}

	@Override
	public AbsenceOrderValue queryByOrderId(Integer orderId) {
		if (orderId != null) {
			return (AbsenceOrderValue) this.getHibernateTemplate().get(AbsenceOrderValue.class, orderId);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AbsenceOrderValue> queryAbsenceOrder(
			final AbsenceOrderValue absenceOrderValue,final Integer rows, final Integer pageNo) {
		String hqlStr = "FROM AbsenceOrderValue aov WHERE 1=1";
		if (absenceOrderValue.getAbsenceCategory() != null) {
			hqlStr += " AND aov.absenceCategory = ?";
		}
		if (absenceOrderValue.getUserItem() != null) {
			hqlStr += " AND aov.userItem = ?";
		}
		if (absenceOrderValue.getStatus() != null) {
			hqlStr += " AND aov.status = ?";
		}
		hqlStr += " ORDER BY aov.createTime DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (absenceOrderValue.getAbsenceCategory() != null) {
					query.setEntity(++index, absenceOrderValue.getAbsenceCategory());
				}
				if (absenceOrderValue.getUserItem() != null) {
					query.setEntity(++index, absenceOrderValue.getUserItem());
				}
				if (absenceOrderValue.getStatus() != null) {
					query.setInteger(++index, absenceOrderValue.getStatus());
				}
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				
				list = query.list();
				return list;
				}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Integer getTotalPageNumber(final AbsenceOrderValue absenceOrderValue, final Integer rows){
		int totalPageNumber;
		String hqlStr = "FROM AbsenceOrderValue aov WHERE 1=1";
		if (absenceOrderValue.getAbsenceCategory() != null) {
			hqlStr += " AND aov.absenceCategory = ?";
		}
		if (absenceOrderValue.getUserItem() != null) {
			hqlStr += " AND aov.userItem = ?";
		}
		if (absenceOrderValue.getStatus() != null) {
			hqlStr += " AND aov.status = ?";
		}
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (absenceOrderValue.getAbsenceCategory() != null) {
					query.setEntity(++index, absenceOrderValue.getAbsenceCategory());
				}
				if (absenceOrderValue.getUserItem() != null) {
					query.setEntity(++index, absenceOrderValue.getUserItem());
				}
				if (absenceOrderValue.getStatus() != null) {
					query.setInteger(++index, absenceOrderValue.getStatus());
				}		
				list = query.list();
				return list;
				}
		});
		totalNumber = list.size();
		if (totalNumber <= rows) {
			totalPageNumber = 1;
		} else {
			if (totalNumber % rows == 0) {
				totalPageNumber = totalNumber / rows;
			} else {
				totalPageNumber = totalNumber / rows + 1;
			}
		}
		return totalPageNumber;
	}
	


	@SuppressWarnings("unchecked")
	@Override
	public List<AbsenceOrderValue> queryAbsenceOrderByDate(final AbsenceOrderValue absenceOrderValue, final String startTime, final String endTime) {
		String hqlStr = "FROM AbsenceOrderValue aov WHERE 1=1";
		if (absenceOrderValue.getAbsenceCategory() != null) {
			hqlStr += " AND aov.absenceCategory = ?";
		}
		if (absenceOrderValue.getUserItem() != null) {
			hqlStr += " AND aov.userItem = ?";
		}
		if (startTime != null) {
			hqlStr += " AND aov.startTime >= ?";
		}
		if (endTime != null) {
			hqlStr += " AND aov.endTime <= ?";
		}
		hqlStr += " AND aov.status = ?";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (absenceOrderValue.getAbsenceCategory() != null) {
					query.setEntity(++index, absenceOrderValue.getAbsenceCategory());
				}
				if (absenceOrderValue.getUserItem() != null) {
					query.setEntity(++index, absenceOrderValue.getUserItem());
				}
				if (startTime != null) {
					query.setString(++index, startTime);
				}
				if (endTime != null) {
					query.setString(++index, endTime);
				}
				query.setInteger(++index, IOrderStatus.PASS);
				list = query.list();
				return list;
				}
		});
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AbsenceOrderValue> queryAbsenceOrderByMonth(final AbsenceOrderValue absenceOrderValue, final String month) {
		String hqlStr = "FROM AbsenceOrderValue aov WHERE 1=1";
		if (absenceOrderValue.getAbsenceCategory() != null) {
			hqlStr += " AND aov.absenceCategory = ?";
		}
		if (absenceOrderValue.getUserItem() != null) {
			hqlStr += " AND aov.userItem = ?";
		}
		if (month != null) {
			hqlStr += " AND extract(year_month from aov.startTime) = ?";
		}
		hqlStr += " AND aov.status = ?";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				int index = -1;
				if (absenceOrderValue.getAbsenceCategory() != null) {
					query.setEntity(++index, absenceOrderValue.getAbsenceCategory());
				}
				if (absenceOrderValue.getUserItem() != null) {
					query.setEntity(++index, absenceOrderValue.getUserItem());
				}
				if (month != null) {
					query.setString(++index, month);
				}
				query.setInteger(++index, IOrderStatus.PASS);
				list = query.list();
				return list;
				}
		});
		return list;
	}

	public List<AbsenceOrderValue> getList() {
		return list;
	}

	public void setList(List<AbsenceOrderValue> list) {
		this.list = list;
	}

	public Integer getTotalNumber() {
		return totalNumber;
	}

	public void setTotalNumber(Integer totalNumber) {
		this.totalNumber = totalNumber;
	}

}
